package com.settlement.system.common.util.common;


import org.apache.commons.lang3.StringUtils;

import java.util.Base64;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class QueryJoinTableUtil {


    /**
     * 大于等于 >=
     * @param sql
     * @return
     */
    private static String convertGTEAccountingPeriod(String sql){
        StringBuffer sqlStringBuffer = new StringBuffer();
        Pattern COMPILE_WHERE = Pattern.compile("WHERE .*tbl_unification_bill_detail.ACCOUNTING_PERIOD\\s*(>\\s*=)\\s*'\\d{6}月'");
        Pattern COMPILE = Pattern.compile("tbl_unification_bill_detail.ACCOUNTING_PERIOD\\s*(>\\s*=)\\s*'\\d{6}月'");
        Pattern COMPILE_ACCOUNTING_PERIOD = Pattern.compile("'\\d{6}月'");
        Matcher whereEq = COMPILE_WHERE.matcher(sql);
        while (whereEq.find()) {
            String whereEqStr = whereEq.group();
            StringBuffer whereEqStrStringBuffer = new StringBuffer();
            Matcher eq = COMPILE.matcher(whereEqStr);
            while(eq.find()){
                String eqStr = eq.group();
                StringBuffer eqStrStringBuffer = new StringBuffer();

                Matcher compilePeriod = COMPILE_ACCOUNTING_PERIOD.matcher(eqStr);

                while (compilePeriod.find()){
                    String accountPeriodStr = compilePeriod.group();
                    String yyyyMM = accountPeriodStr.replace("月","");
                    String firstDayOfThisMonth = "to_date("+yyyyMM+",'yyyyMM')";
                    compilePeriod.appendReplacement(eqStrStringBuffer, firstDayOfThisMonth +" ");
                }
                compilePeriod.appendTail(eqStrStringBuffer);
                eq.appendReplacement(whereEqStrStringBuffer, eqStrStringBuffer.toString().replace("tbl_unification_bill_detail.ACCOUNTING_PERIOD","TBL_UNIFICATION_BILL_DETAIL.OUT_DATE") +" ");
            }
            eq.appendTail(whereEqStrStringBuffer);
            whereEq.appendReplacement(sqlStringBuffer, whereEqStrStringBuffer.toString() +" ");
        }
        whereEq.appendTail(sqlStringBuffer);
        if(StringUtils.isNotEmpty(sqlStringBuffer.toString())){
            sql = sqlStringBuffer.toString();

        }
        return sql;
    }

    /**
     * BETWEEN 介于
     * @param sql
     * @return
     */
    private static String convertBTccountingPeriod(String sql){
        Pattern COMPILE_ACCOUNTING_PERIOD_CONDITION_WHERE_BT = Pattern.compile("WHERE .*TBL_UNIFICATION_BILL_DETAIL.ACCOUNTING_PERIOD\\s*BETWEEN\\s*'\\d{6}月'\\s*AND\\s*'\\d{6}月'");
        Pattern COMPILE_ACCOUNTING_PERIOD_CONDITION_BT = Pattern.compile("TBL_UNIFICATION_BILL_DETAIL.ACCOUNTING_PERIOD\\s*BETWEEN\\s*'\\d{6}月'\\s*AND\\s*'\\d{6}月'");
        Pattern COMPILE_ACCOUNTING_PERIOD = Pattern.compile("\\d{6}月");

        Matcher whereEq = COMPILE_ACCOUNTING_PERIOD_CONDITION_WHERE_BT.matcher(sql);
        while (whereEq.find()) {
            String whereEqStr = whereEq.group();
            Matcher eq = COMPILE_ACCOUNTING_PERIOD_CONDITION_BT.matcher(whereEqStr);
            while(eq.find()){
                String eqStr = eq.group();
                Matcher compilePeriod = COMPILE_ACCOUNTING_PERIOD.matcher(eqStr);

                String startDateStr = "";
                String endDateStr = "";

                if(compilePeriod.find()){
                    String accountPeriodStr = compilePeriod.group();
                    String yyyyMM = accountPeriodStr.replace("月","");
                    String firstDayOfThisMonth = "to_date('"+yyyyMM+"','yyyyMM')";
                    startDateStr = firstDayOfThisMonth;
                }
                if(compilePeriod.find()){
                    String accountPeriodStr = compilePeriod.group();
                    String yyyyMM = accountPeriodStr.replace("月","");
                    String endDayOfThisMonth = "add_months(to_date('"+yyyyMM+"','yyyyMM'),1) - 1 ";
                    endDateStr = endDayOfThisMonth;
                }

                sql = sql.replaceAll(eqStr," ( TBL_UNIFICATION_BILL_DETAIL.OUT_DATE >= "+startDateStr + " AND TBL_UNIFICATION_BILL_DETAIL.OUT_DATE <= "+endDateStr+" ) ");


            }
        }
        return sql;
    }

    /**
     * IN 包含
     * @param sql
     * @return
     */
    private static String convertINccountingPeriod(String sql){
        Pattern COMPILE_ACCOUNTING_PERIOD_CONDITION_WHERE_BT = Pattern.compile("WHERE .*TBL_UNIFICATION_BILL_DETAIL.ACCOUNTING_PERIOD\\s*IN\\s*\\(\\s*('\\d{6}月'\\s*,\\s*)*'\\d{6}月'\\s*\\)");
        Pattern COMPILE_ACCOUNTING_PERIOD_CONDITION_BT = Pattern.compile("TBL_UNIFICATION_BILL_DETAIL.ACCOUNTING_PERIOD\\s*IN\\s*\\(\\s*('\\d{6}月'\\s*,\\s*)*'\\d{6}月'\\s*\\)");
        Pattern COMPILE_ACCOUNTING_PERIOD = Pattern.compile("'\\d{6}月'");

        Matcher whereEq = COMPILE_ACCOUNTING_PERIOD_CONDITION_WHERE_BT.matcher(sql);
        while (whereEq.find()) {
            String whereEqStr = whereEq.group();
            Matcher eq = COMPILE_ACCOUNTING_PERIOD_CONDITION_BT.matcher(whereEqStr);
            while(eq.find()){
                String eqStr = eq.group();
                String eqStrBf = eqStr;
                String subStr = "";
                Matcher compilePeriod = COMPILE_ACCOUNTING_PERIOD.matcher(eqStr);

                String startDateStr = "";
                String endDateStr = "";

                while(compilePeriod.find()){
                    String accountPeriodStr = compilePeriod.group();
                    int i = eqStr.indexOf(accountPeriodStr);
                    if(StringUtils.isEmpty(subStr)){
                        subStr = eqStr.substring(0, i);
                    }

                    String yyyyMM = accountPeriodStr.replace("月","");
                    String firstDayOfThisMonth = "to_date("+yyyyMM+",'yyyyMM')";
                    startDateStr = firstDayOfThisMonth;
                    eqStr = eqStr.replace(accountPeriodStr, "TBL_UNIFICATION_BILL_DETAIL.ACCOUNTING_PERIOD = "+accountPeriodStr);
                }
                eqStr = eqStr.replace(","," OR ")
                        .replace(subStr," ( ");
                sql = sql.replace(eqStrBf,eqStr);


            }
        }
        return sql;
    }

    /**
     * 大于  >
     * @param sql
     * @return
     */
    private static String convertGTAccountingPeriod(String sql){
        StringBuffer sqlStringBuffer = new StringBuffer();
        Pattern COMPILE_WHERE = Pattern.compile("WHERE .*TBL_UNIFICATION_BILL_DETAIL.ACCOUNTING_PERIOD\\s*(>)\\s*'\\d{6}月'");
        Pattern COMPILE = Pattern.compile("TBL_UNIFICATION_BILL_DETAIL.ACCOUNTING_PERIOD\\s*(>)\\s*'\\d{6}月'");
        Pattern COMPILE_ACCOUNTING_PERIOD = Pattern.compile("'\\d{6}月'");
        Matcher whereEq = COMPILE_WHERE.matcher(sql);
        while (whereEq.find()) {
            String whereEqStr = whereEq.group();
            StringBuffer whereEqStrStringBuffer = new StringBuffer();
            Matcher eq = COMPILE.matcher(whereEqStr);
            while(eq.find()){
                String eqStr = eq.group();
                StringBuffer eqStrStringBuffer = new StringBuffer();

                Matcher compilePeriod = COMPILE_ACCOUNTING_PERIOD.matcher(eqStr);

                while (compilePeriod.find()){
                    String accountPeriodStr = compilePeriod.group();
                    String yyyyMM = accountPeriodStr.replace("月","");
                    String endDayOfThisMonth = "add_months(to_date("+yyyyMM+",'yyyyMM'),1) - 1 ";
                    compilePeriod.appendReplacement(eqStrStringBuffer, endDayOfThisMonth +" ");
                }
                compilePeriod.appendTail(eqStrStringBuffer);
                eq.appendReplacement(whereEqStrStringBuffer, eqStrStringBuffer.toString().replace("TBL_UNIFICATION_BILL_DETAIL.ACCOUNTING_PERIOD","TBL_UNIFICATION_BILL_DETAIL.OUT_DATE") +" ");
            }
            eq.appendTail(whereEqStrStringBuffer);
            whereEq.appendReplacement(sqlStringBuffer, whereEqStrStringBuffer.toString() +" ");
        }
        whereEq.appendTail(sqlStringBuffer);
        if(StringUtils.isNotEmpty(sqlStringBuffer.toString())){
            sql = sqlStringBuffer.toString();

        }
        return sql;
    }

    /**
     * 小于 <
     * @param sql
     * @return
     */
    private static String convertLTAccountingPeriod(String sql){
        StringBuffer sqlStringBuffer = new StringBuffer();
        Pattern COMPILE_WHERE = Pattern.compile("WHERE .*TBL_UNIFICATION_BILL_DETAIL.ACCOUNTING_PERIOD\\s*(<)\\s*'\\d{6}月'");
        Pattern COMPILE = Pattern.compile("TBL_UNIFICATION_BILL_DETAIL.ACCOUNTING_PERIOD\\s*(<)\\s*'\\d{6}月'");
        Pattern COMPILE_ACCOUNTING_PERIOD = Pattern.compile("'\\d{6}月'");
        Matcher whereEq = COMPILE_WHERE.matcher(sql);
        while (whereEq.find()) {
            String whereEqStr = whereEq.group();
            StringBuffer whereEqStrStringBuffer = new StringBuffer();
            Matcher eq = COMPILE.matcher(whereEqStr);
            while(eq.find()){
                String eqStr = eq.group();
                StringBuffer eqStrStringBuffer = new StringBuffer();

                Matcher compilePeriod = COMPILE_ACCOUNTING_PERIOD.matcher(eqStr);

                while (compilePeriod.find()){
                    String accountPeriodStr = compilePeriod.group();
                    String yyyyMM = accountPeriodStr.replace("月","");
                    String firstDayOfThisMonth = "to_date("+yyyyMM+",'yyyyMM')";
                    compilePeriod.appendReplacement(eqStrStringBuffer, firstDayOfThisMonth +" ");
                }
                compilePeriod.appendTail(eqStrStringBuffer);
                eq.appendReplacement(whereEqStrStringBuffer, eqStrStringBuffer.toString().replace("TBL_UNIFICATION_BILL_DETAIL.ACCOUNTING_PERIOD","TBL_UNIFICATION_BILL_DETAIL.OUT_DATE") +" ");
            }
            eq.appendTail(whereEqStrStringBuffer);
            whereEq.appendReplacement(sqlStringBuffer, whereEqStrStringBuffer.toString() +" ");
        }
        whereEq.appendTail(sqlStringBuffer);
        if(StringUtils.isNotEmpty(sqlStringBuffer.toString())){
            sql = sqlStringBuffer.toString();
        }
        return sql;
    }

    /**
     * 小于等于 <=
     * @param sql
     * @return
     */
    private static String convertLTEAccountingPeriod(String sql){
        StringBuffer sqlStringBuffer = new StringBuffer();
        Pattern COMPILE_WHERE = Pattern.compile("WHERE .*TBL_UNIFICATION_BILL_DETAIL.ACCOUNTING_PERIOD\\s*(<\\s*=)\\s*'\\d{6}月'");
        Pattern COMPILE = Pattern.compile("TBL_UNIFICATION_BILL_DETAIL.ACCOUNTING_PERIOD\\s*(<\\s*=)\\s*'\\d{6}月'");
        Pattern COMPILE_ACCOUNTING_PERIOD = Pattern.compile("'\\d{6}月'");
        Matcher whereEq = COMPILE_WHERE.matcher(sql);
        while (whereEq.find()) {
            String whereEqStr = whereEq.group();
            StringBuffer whereEqStrStringBuffer = new StringBuffer();
            Matcher eq = COMPILE.matcher(whereEqStr);
            while(eq.find()){
                String eqStr = eq.group();
                StringBuffer eqStrStringBuffer = new StringBuffer();

                Matcher compilePeriod = COMPILE_ACCOUNTING_PERIOD.matcher(eqStr);

                while (compilePeriod.find()){
                    String accountPeriodStr = compilePeriod.group();
                    String yyyyMM = accountPeriodStr.replace("月","");
                    String endDayOfThisMonth = "add_months(to_date("+yyyyMM+",'yyyyMM'),1) - 1 ";
                    compilePeriod.appendReplacement(eqStrStringBuffer, endDayOfThisMonth +" ");
                }
                compilePeriod.appendTail(eqStrStringBuffer);
                eq.appendReplacement(whereEqStrStringBuffer, eqStrStringBuffer.toString().replace("TBL_UNIFICATION_BILL_DETAIL.ACCOUNTING_PERIOD","TBL_UNIFICATION_BILL_DETAIL.OUT_DATE") +" ");
            }
            eq.appendTail(whereEqStrStringBuffer);
            whereEq.appendReplacement(sqlStringBuffer, whereEqStrStringBuffer.toString() +" ");
        }
        whereEq.appendTail(sqlStringBuffer);
        if(StringUtils.isNotEmpty(sqlStringBuffer.toString())){
            sql = sqlStringBuffer.toString();

        }
        return sql;
    }

    /**
     * 不等于  !=  <>
     * @param sql
     * @return
     */
    private static String convertNEQAccountingPeriod(String sql){
        Pattern COMPILE_ACCOUNTING_PERIOD_CONDITION_WHERE_NEQ = Pattern.compile("WHERE .*TBL_UNIFICATION_BILL_DETAIL.ACCOUNTING_PERIOD\\s*((!\\s*=)|(<\\s*>))\\s*'\\d{6}月'");
        Pattern COMPILE_ACCOUNTING_PERIOD_CONDITION_NEQ = Pattern.compile("TBL_UNIFICATION_BILL_DETAIL.ACCOUNTING_PERIOD\\s*((!\\s*=)|(<\\s*>))\\s*'\\d{6}月'");
        Pattern COMPILE_ACCOUNTING_PERIOD = Pattern.compile("\\d{6}月");

        Matcher whereEq = COMPILE_ACCOUNTING_PERIOD_CONDITION_WHERE_NEQ.matcher(sql);
        while (whereEq.find()) {
            String whereEqStr = whereEq.group();
            Matcher eq = COMPILE_ACCOUNTING_PERIOD_CONDITION_NEQ.matcher(whereEqStr);
            while(eq.find()){
                String eqStr = eq.group();
                Matcher compilePeriod = COMPILE_ACCOUNTING_PERIOD.matcher(eqStr);

                if(compilePeriod.find()){
                    String accountPeriodStr = compilePeriod.group();
                    String yyyyMM = accountPeriodStr.replace("月","");
                    String firstDayOfThisMonth = "to_date('"+yyyyMM+"','yyyyMM')";
                    String endDayOfThisMonth = "add_months(to_date('"+yyyyMM+"','yyyyMM'),1) - 1 ";
                    sql = sql.replaceAll(eqStr," ( TBL_UNIFICATION_BILL_DETAIL.OUT_DATE < "+firstDayOfThisMonth + " OR TBL_UNIFICATION_BILL_DETAIL.OUT_DATE > "+endDayOfThisMonth+" ) ");
                }
            }
        }
        return sql;
    }

    /**
     * 等于
     * @param sql
     * @return
     */
    private static String convertEqAccountingPeriod(String sql){
        Pattern COMPILE_ACCOUNTING_PERIOD_CONDITION_WHERE_EQ = Pattern.compile("WHERE .*TBL_UNIFICATION_BILL_DETAIL.ACCOUNTING_PERIOD\\s*=\\s*'\\d{6}月'");
        Pattern COMPILE_ACCOUNTING_PERIOD_CONDITION_EQ = Pattern.compile("TBL_UNIFICATION_BILL_DETAIL.ACCOUNTING_PERIOD\\s*=\\s*'\\d{6}月'");
        Pattern COMPILE_ACCOUNTING_PERIOD = Pattern.compile("\\d{6}月");

        Matcher whereEq = COMPILE_ACCOUNTING_PERIOD_CONDITION_WHERE_EQ.matcher(sql);
        while (whereEq.find()) {
            String whereEqStr = whereEq.group();
            Matcher eq = COMPILE_ACCOUNTING_PERIOD_CONDITION_EQ.matcher(whereEqStr);
            while(eq.find()){
                String eqStr = eq.group();
                Matcher compilePeriod = COMPILE_ACCOUNTING_PERIOD.matcher(eqStr);

                if(compilePeriod.find()){
                    String accountPeriodStr = compilePeriod.group();
                    String yyyyMM = accountPeriodStr.replace("月","");
                    String firstDayOfThisMonth = "to_date('"+yyyyMM+"','yyyyMM')";
                    String endDayOfThisMonth = "add_months(to_date('"+yyyyMM+"','yyyyMM'),1) - 1 ";
                    sql = sql.replaceAll(eqStr," ( TBL_UNIFICATION_BILL_DETAIL.OUT_DATE >= "+firstDayOfThisMonth + " AND TBL_UNIFICATION_BILL_DETAIL.OUT_DATE <= "+endDayOfThisMonth+" ) ");
                }
            }
        }
        return sql;
    }


    public static String encode(String str){
        if(!StringUtils.startsWith(str,"ACS_")){
            String s = Base64.getEncoder().encodeToString(str.getBytes());
            return "ACS_"+s;
        }
        return str;
    }

    public static String decode(String str){
        if(StringUtils.startsWith(str,"ACS_")){
            byte[] decode = Base64.getDecoder().decode(str.substring(4).getBytes());
            String s = new String(decode);
            return s;
        }
        return str;
    }

    public static String convertAccountingPeriodToOutDate(String str){
        if(StringUtils.contains(str,"TBL_UNIFICATION_BILL_DETAIL.ACCOUNTING_PERIOD")){
            str = convertINccountingPeriod(str);
            str = convertBTccountingPeriod(str);
            str = convertEqAccountingPeriod(str);
            str = convertGTAccountingPeriod(str);
            str = convertGTEAccountingPeriod(str);
            str = convertLTAccountingPeriod(str);
            str = convertLTEAccountingPeriod(str);
            str = convertNEQAccountingPeriod(str);
        }
        return str;
    }
}
